package com.example.demo.util;

import com.example.demo.bean.ExcelData;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;


/**
 * @program: SpringAOP
 * @ClassName ExcelUtils
 * @description:
 * @author: zsn
 * @create: 2024-11-19 11:01
 * @Version 1.0
 **/
public class ExcelUtils {

    /**
     * @Description: 入口方法
     */
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data){
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //下载文件默认名称
        try {
            response.reset();
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(fileName +".xlsx","utf-8"));
            exportExcel(data,response.getOutputStream());
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static int exportExcel(ExcelData data, OutputStream out) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        int rowIndex = 0;

        try {
            String sheetName = data.getName();
            if (null == sheetName){
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            rowIndex = writeExcel(wb, sheet, data);
            wb.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            out.close();
        }
        return rowIndex;
    }

    /**
     * 表显示字段
     */
    public static int writeExcel(XSSFWorkbook wb, XSSFSheet sheet, ExcelData data){
        int rowIndex = 0;
        rowIndex = writeTitleTOExcel(wb, (XSSFSheet) sheet,data.getTitles());
        rowIndex = wriRowsToExcel(wb, sheet,data.getRows(),rowIndex);
        autoSizeColumns(sheet,data.getTitles().size() + 1);
        return rowIndex;
    }
    /**
     * 设置表头
     */
    public static int writeTitleTOExcel(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {

        int rowIndex = 0;
        int colIndex = 0;
        XSSFFont titleFont = wb.createFont();
        //设置字体
        titleFont.setFontName("simsun");
        //设置粗体
        titleFont.setBold(true);
        //设置字号
        titleFont.setFontHeightInPoints((short) 14);
        //设置颜色
        titleFont.setColor(IndexedColors.BLACK.index);
        XSSFCellStyle titleStyle = wb.createCellStyle();
        //水平居中
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
//        titleStyle.setVerticalAlignment(HorizontalAlignment.VERTICAL_CENTER);
        //设置图案颜色
//        titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(182,184,192)));
        //设置图案样式
//        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
//        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
        Row titleRow = sheet.createRow(rowIndex);
        titleRow.setHeightInPoints(25);
        colIndex = 0;
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        rowIndex++;
        return rowIndex;
    }
    /**
     * 设置内容
     */
    public static int wriRowsToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex;
        XSSFFont dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
//        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
//        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        for (int i = 0; i < rows.size(); i++) {
            List<Object> rowData = rows.get(i);
            Row dataRow = sheet.createRow(rowIndex);
            dataRow.setHeightInPoints(25);
            colIndex = 0;
            for (int j = 0; j < rowData.size(); j++) {
                Object object = rowData.get(j);
                Cell cell = dataRow.createCell(colIndex);
                XSSFDrawing drawing = sheet.createDrawingPatriarch();
                if (rowData.get(j) != null){
                    if (rowData.get(j) instanceof byte[]){
                        //anchor主要用于设置图片的属性
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,(short) j, 1+i, (short) j+1, 2+i);
                        anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                        //插入图片
                        drawing.createPicture(anchor, wb.addPicture((byte[]) rowData.get(j), XSSFWorkbook.PICTURE_TYPE_JPEG));
                    }else {
                        cell.setCellValue(rowData.get(j).toString());
                    }
                }else {
                    cell.setCellValue("");
                }
                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 自动调整列宽
     */
    public static void autoSizeColumns(XSSFSheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++){
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i,true);
        }
    }

    /**
     * 设置边框
     */
    public static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderBottom(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT,color);
    }

}